package com.ng.biz.controller;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
import com.influxdb.client.InfluxDBClient;
import com.influxdb.client.QueryApi;
import com.influxdb.query.FluxRecord;
import com.influxdb.query.FluxTable;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.ng.biz.model.domain.Sql;
import com.ng.biz.model.domain.SqlCategory;
import com.ng.biz.model.request.SqlTestRequest;
import com.ng.biz.service.IDatasource;
import com.ng.common.controller.Controller;
import com.ng.common.model.dto.ParsedSql;
import com.ng.common.model.response.R;
import com.ng.common.util.ParsedSqlUtil;
import com.ng.common.util.UuidUtil;
import com.ng.common.util.WebUtil;

import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.Parameter;
import io.swagger.v3.oas.annotations.Parameters;
import io.swagger.v3.oas.annotations.tags.Tag;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.ArrayList;
import java.util.HashMap;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

@Slf4j
@Tag(name = "SQL管理")
@RestController
@RequestMapping("/biz/sql")
public class SqlController extends Controller{
	
	@Autowired
	private IDatasource datasourceImpl;

    @Operation(summary = "获取SQL类别列表")
    @Parameters({
        @Parameter(name = "keywords", description = "关键字")
    })
    @GetMapping("/category/page")
    public R<List<SqlCategory>> getCategoryPage(@RequestParam(name = "name", required = false, defaultValue = "") String keywords) {
		String sqlExceptSelect = " from " + SqlCategory.TABLE_NAME + "  a WHERE 1=1 ";
		if (!StrKit.isBlank(keywords)) {
			sqlExceptSelect += " and a.name like '%" + keywords + "%' ";
		}
		sqlExceptSelect += "  ORDER BY a.sort asc ";
		return R.ok(SqlCategory.dao.find("select a.* " + sqlExceptSelect));
    }

    @Operation(summary = "保存SQL类别")
    @PostMapping("/category/save")
    public R<SqlCategory> saveCategory(HttpServletRequest request, @RequestBody SqlCategory category) {
        try {
            if (category.getId() == null) {
            	category.setId(UuidUtil.getUUID32());
            	category.setCreateUserId(getUserId(request));
            	category.setCreateTime(new Date(System.currentTimeMillis()));
            	if(category.save()) {
            		return R.ok(category);
            	}
            } else {
            	category.setUpdateTime(new Date(System.currentTimeMillis()));
            	category.setUpdateUserId(getUserId(request));
            	if(category.update()) {
            		  return R.ok(category);
            	}
            }
          
        } catch (Exception e) {
            log.error("保存SQL类别失败", e);
            return R.failed("保存SQL类别失败");
        }
		return  R.failed("保存SQL类别失败");
    }

    @Operation(summary = "删除SQL类别")
    @DeleteMapping("/category/{ids}")
    public R<Boolean> deleteCategory(@PathVariable("ids") String ids) {
        try {
    		String[] arr=ids.split(",");
    		if(ids==null || ids.length()<=0) {
    			return R.failed("请选择需要删除的SQL类别.");
    		}
    		for(String id:arr) {
    			SqlCategory.dao.deleteByIds(id);
    			Db.delete("delete from "+Sql.TABLE_NAME+" where category_id=?",id);
    		}
            return R.ok(true);
        } catch (Exception e) {
            log.error("删除SQL类别失败", e);
            return R.failed("删除SQL类别失败");
        }
    }

    @Operation(summary = "获取SQL分页列表")
    @Parameters({
        @Parameter(name = "keywords", description = "关键字"),
        @Parameter(name = "categoryId", description = "类别ID"),
        @Parameter(name = "cur", description = "当前页"),
        @Parameter(name = "limit", description = "每页条数")
    })
    @GetMapping("/page")
    public R<Page<Sql>> getModelPage(
            @RequestParam(name = "keywords",required = false) String keywords,
            @RequestParam(name = "categoryId",required = false) String categoryId,
            @RequestParam(name = "cur",defaultValue = "1") Integer cur,
            @RequestParam(name = "limit",defaultValue = "10") Integer limit) {
        try {
    		String sqlExceptSelect = " from " + Sql.TABLE_NAME + "  a WHERE 1=1 ";
    		if (!StrKit.isBlank(keywords)) {
    			sqlExceptSelect += " and (a.name like '%" + keywords + "%' ) ";
    		}
    		if (!StrKit.isBlank(categoryId)) {
    			sqlExceptSelect += " and a.category_id = '" + categoryId + "'  ";
    		}
    		sqlExceptSelect += "  ORDER BY a.sort asc ";
        	
            Page<Sql> page = Sql.dao.paginate(cur, limit, "select a.* ", sqlExceptSelect);
            return R.ok(page);
        } catch (Exception e) {
            log.error("获取SQL分页列表失败", e);
            return R.failed("获取SQL分页列表失败");
        }
    }

    @Operation(summary = "保存SQL")
    @PostMapping("/save")
    public R<Sql> saveModel(HttpServletRequest request, @RequestBody Sql model) {
        try {
            if (model.getId() == null) {
            	model.setId(UuidUtil.getUUID32());
            	model.setCreateUserId(getUserId(request));
            	model.setCreateTime(new Date(System.currentTimeMillis()));
            	if(model.save()) {
            		return R.ok(model);
            	}
            } else {
            	model.setUpdateTime(new Date(System.currentTimeMillis()));
            	model.setUpdateUserId(getUserId(request));
                if(model.update()) {
            		return R.ok(model);
            	}
            }
        } catch (Exception e) {
            log.error("保存SQL失败", e);
        }
        return R.failed("保存SQL失败");
    }

    @Operation(summary = "删除SQL")
    @DeleteMapping("/{ids}")
    public R<Boolean> deleteModel(@PathVariable("ids") String ids) {
        try {
    		String[] arr=ids.split(",");
    		if(ids==null || ids.length()<=0) {
    			return R.failed("请选择需要删除的SQL.");
    		}
    		for(String id:arr) {
    			Sql.dao.deleteByIds(id);
    		}
            return R.ok(true);
        } catch (Exception e) {
            log.error("删除SQL失败", e);
            return R.failed("删除SQL失败");
        }
    }

    @Operation(summary = "执行SQL测试")
    @PostMapping("/test")
    public void testSql(HttpServletRequest request, HttpServletResponse response, @RequestBody SqlTestRequest sqlTestRequest) {
        try {
        	
        	Sql sql=Sql.dao.findById(sqlTestRequest.getId());
        	if(sql==null) {
        		WebUtil.renderJson(response,R.failed("sql信息不存在."));
        		return;
        	}
        	
        	
        	JSONObject contentJSONObject=JSONObject.parseObject(sql.getContent());
        	if(contentJSONObject==null || contentJSONObject.isEmpty()) {
        		WebUtil.renderJson(response,R.failed("sql信息不存在."));
        		return;
        	}
        	
        	
        	if(StrKit.equals("InfluxDB", sql.getType())) {

        		// 解析查询参数
        		ParsedSql parsedSql = ParsedSqlUtil.parseInfluxDBFlux(
        			contentJSONObject.getString("sql"), 
        			JSON.parseObject(JSONObject.toJSONString(contentJSONObject.getJSONArray("params")), 
        			new TypeReference<List<JSONObject>>() {}), 
        			sqlTestRequest.getValues()
        		);

        		if(parsedSql == null) {
        			WebUtil.renderJson(response, R.failed("解析InfluxDB查询语句失败"));
        			return;
        		}

        		// 创建 InfluxDB 客户端
        		InfluxDBClient influxDBClient = datasourceImpl.getInfluxDBClient(sql.getDatasourceId());
        		try {

        			// 构建查询
        			String query = parsedSql.getSql();
        			QueryApi queryApi = influxDBClient.getQueryApi();

        			JSONObject result = new JSONObject();
        			
        			// 根据执行类型执行不同的查询
        			switch(sql.getExec()) {
        				case "query":
        					// 执行 Flux 查询并获取结果
        					List<FluxTable> tables = queryApi.query(query);
        					List<Map<String, Object>> records = new ArrayList<>();
        					
        					for (FluxTable table : tables) {
        						for (FluxRecord record : table.getRecords()) {
        							Map<String, Object> row = new HashMap<>();
        							record.getValues().forEach((k, v) -> row.put(k, v));
        							records.add(row);
        						}
        					}
        					result.put(sql.getIdentifier(), records);
        					break;
        					
        				case "queryRaw":
        					// 执行原始查询
        					String rawResult = queryApi.queryRaw(query);
        					result.put(sql.getIdentifier(), rawResult);
        					break;
        					
        				case "queryFirst":
        					// 获取第一条记录
        					List<FluxTable> firstTable = queryApi.query(query);
        					if (!firstTable.isEmpty() && !firstTable.get(0).getRecords().isEmpty()) {
        						FluxRecord firstRecord = firstTable.get(0).getRecords().get(0);
        						Map<String, Object> firstRow = new HashMap<>();
        						firstRecord.getValues().forEach((k, v) -> firstRow.put(k, v));
        						result.put(sql.getIdentifier(), firstRow);
        					} else {
        						result.put(sql.getIdentifier(), null);
        					}
        					break;
        				default:
        					throw new Exception("不支持的InfluxDB执行类型: " + sql.getExec());
        			}
        			
        			WebUtil.renderJson(response, R.ok(result));
        			
        		} finally {
        			if(influxDBClient != null) {
        				influxDBClient.close();
        			}
        		}
        		return;
        	}

        	JSONObject jsonObject=new JSONObject();
        	
    		if (StrKit.equals("save", sql.getExec())) {
    			com.jfinal.plugin.activerecord.Record record = ParsedSqlUtil.parseSqlToRecord(contentJSONObject.getString("sql"), JSON.parseObject(JSONObject.toJSONString(contentJSONObject.getJSONArray("params")),
    					new TypeReference<List<JSONObject>>() {}), sqlTestRequest.getValues());
    			if(record==null) {
    				throw new Exception("保存失败，请检查参数是否正确: " + sql.getExec());
    			}
    			jsonObject.put(sql.getIdentifier(),Db.use(sql.getDatasourceId()).save(sql.getTable(), sql.getPrimaryKey(), record));
    			WebUtil.renderJson(response, R.ok(jsonObject));
    			return;
    		}
    		
    		if(StrKit.equals("update", sql.getExec())) {
    			com.jfinal.plugin.activerecord.Record record = ParsedSqlUtil.parseSqlToRecord(contentJSONObject.getString("sql"), JSON.parseObject(JSONObject.toJSONString(contentJSONObject.getJSONArray("params")),
    					new TypeReference<List<JSONObject>>() {}), sqlTestRequest.getValues());
    			if(record==null) {
    				throw new Exception("更新失败，请检查参数是否正确: " + sql.getExec());
    			}
    			jsonObject.put(sql.getIdentifier(),Db.use(sql.getDatasourceId()).update(sql.getTable(), sql.getPrimaryKey(), record));
    			WebUtil.renderJson(response, R.ok(jsonObject));
    			return;
    		}

        	ParsedSql parsedSql=ParsedSqlUtil.parseSql(contentJSONObject.getString("sql"), JSON.parseObject(  JSONObject.toJSONString(contentJSONObject.getJSONArray("params")), new TypeReference<List<JSONObject>>() {}), sqlTestRequest.getValues());
        	
        	if(parsedSql==null) {
        		WebUtil.renderJson(response,R.failed("解析sql失败."));
        		return;
        	}
        	
        	
        	if (StrKit.equals("find", sql.getExec())) {
    			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).find(parsedSql.getSql(),parsedSql.getParams().toArray()));
        	} 
    		else if (StrKit.equals("findFirst", sql.getExec())) {
    			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).findFirst(parsedSql.getSql(),parsedSql.getParams().toArray()));
    		} 
    		else if (StrKit.equals("delete", sql.getExec())) {
    			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).delete(parsedSql.getSql(),parsedSql.getParams().toArray()));
    		} 
    		else if (StrKit.equals("queryBigDecimal", sql.getExec())) {
    			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryBigDecimal(parsedSql.getSql(),parsedSql.getParams().toArray()));
    		} 
    		else if (StrKit.equals("queryBoolean", sql.getExec())) {
    			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryBoolean(parsedSql.getSql(),parsedSql.getParams().toArray()));
    		} 
    		else if (StrKit.equals("queryByte", sql.getExec())) {
    			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryByte(parsedSql.getSql(),parsedSql.getParams().toArray()));
    		} 
    		else if (StrKit.equals("queryDate", sql.getExec())) {
    			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryDate(parsedSql.getSql(),parsedSql.getParams().toArray()));
    		} 
    		else if (StrKit.equals("queryDouble", sql.getExec())) {
    			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryDouble(parsedSql.getSql(),parsedSql.getParams().toArray()));
    		} 
    		else if (StrKit.equals("queryFloat", sql.getExec())) {
    			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryFloat(parsedSql.getSql(),parsedSql.getParams().toArray()));
    		}
    		else if (StrKit.equals("queryInt", sql.getExec())) {
    			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryInt(parsedSql.getSql(),parsedSql.getParams().toArray()));
    		}
    		else if (StrKit.equals("queryLong", sql.getExec())) {
    			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryLong(parsedSql.getSql(),parsedSql.getParams().toArray()));
    		}
    		else if (StrKit.equals("queryNumber", sql.getExec())) {
    			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryNumber(parsedSql.getSql(),parsedSql.getParams().toArray()));
    		}
    		else if (StrKit.equals("queryShort", sql.getExec())) {
    			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryShort(parsedSql.getSql(),parsedSql.getParams().toArray()));
    		}
    		else if (StrKit.equals("queryStr", sql.getExec())) {
    			jsonObject.put(sql.getIdentifier(), Db.use(sql.getDatasourceId()).queryStr(parsedSql.getSql(),parsedSql.getParams().toArray()));
    		}
    		else if (StrKit.equals("batch", sql.getExec())) {
    			throw new Exception(String.format("不支持执行类型:{}.", sql.getExec()));
    		} else {
    			throw new Exception(String.format("未识别的执行类型:{}.", sql.getExec()));
    		}
            WebUtil.renderJson(response, R.ok(jsonObject));
        } catch (Exception e) {
            log.error("执行SQL测试失败", e);
            WebUtil.renderJson(response, R.failed("执行SQL测试失败: " + e.getMessage()));
        }
    }

    @Operation(summary = "获取SQL下拉列表")
    @Parameters({
        @Parameter(name = "keywords", description = "关键字"),
        @Parameter(name = "categoryId", description = "类别ID"),
    })
    @GetMapping("/options")
    public R<List<Sql>> options(
            @RequestParam(name = "keywords",required = false) String keywords,
            @RequestParam(name = "categoryId",required = false) String categoryId) {
   		String sqlExceptSelect = " from " + Sql.TABLE_NAME + "  a WHERE 1=1 ";
		if (!StrKit.isBlank(keywords)) {
			sqlExceptSelect += " and (a.name like '%" + keywords + "%' ) ";
		}
		if (!StrKit.isBlank(categoryId)) {
			sqlExceptSelect += " and a.category_id = '" + categoryId + "'  ";
		}
		sqlExceptSelect += "  ORDER BY a.sort asc ";
        List<Sql> list = Sql.dao.find("select a.* "+ sqlExceptSelect);
        return R.ok(list);
    }
}
